package com.javasm.dao.MisMannageDao.impl;


import com.javasm.dao.MisMannageDao.ProductListDao;
import com.javasm.entity.MisMannageEntity.ProdInfo;
import com.javasm.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class ProductListDaoImpl implements ProductListDao {

    @Override
    public List<ProdInfo> getProdlist(String seriesName) {
         Connection conn = JDBCUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<ProdInfo> prodInfos = new LinkedList<>();
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT pi.prod_id," +
                "                pi.prod_name," +
                "                pi.series_id," +
                "                ps.series_CHname," +
                "                pi.second_type_id," +
                "                tt.type_name," +
                "                pi.opening_time," +
                "                pi.money_type," +
                "                sh.subscribe_upwards " + //线上申购
                "from tb_prod_info pi ,tb_prod_series ps ,tb_show sh , tb_type tt " +
                "WHERE pi.series_id = ps.series_id " +
                "and pi.second_type_id= tt.type_id " +
                "and sh.show_prod_id = pi.prod_id " +
                "and ps.series_CHname = '"+seriesName+"'");

        try {
            preparedStatement = conn.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ProdInfo prodInfo = new ProdInfo();
                prodInfo.setProdId(resultSet.getInt("pi.prod_id"));
                prodInfo.setProdName(resultSet.getString("pi.prod_name"));
                prodInfo.setSeriesId(resultSet.getInt("pi.series_id"));
                prodInfo.setSeriesName(resultSet.getString("ps.series_CHname"));
                prodInfo.setSecondTypeId(resultSet.getInt("pi.second_type_id"));
                prodInfo.setSecondTypeName(resultSet.getString("tt.type_name"));
                prodInfo.setOpeningTime(resultSet.getDate("pi.opening_time"));
                prodInfo.setMoneyType(resultSet.getString("pi.money_type"));
                prodInfo.setSubscribeUpwards(resultSet.getBoolean("sh.subscribe_upwards"));
                prodInfos.add(prodInfo);
            }
            System.out.println(prodInfos);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.closeConn(conn, preparedStatement,null, resultSet);
        }

        return prodInfos;
    }
}
